#!/bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive	

${HIVE_HOME} -S -e "
create database if not EXISTS intention_dws;
use intention_dws;

drop table if EXISTS intention_dws.dws_intention;
create table intention_dws.dws_intention(
yearinfo        STRING comment '年',
monthinfo         STRING comment '月',
dayinfo             STRING comment '日' ,
hourinfo                STRING comment '小时',
O2O                  STRING COMMENT 'O2O' ,
customer_mark           STRING COMMENT '新老客户',
area                     STRING COMMENT '地区' ,
itcast_subject_id          INT COMMENT '学科id' ,
itcast_subject_name         STRING COMMENT '学科name' ,
itcast_school_id               INT COMMENT '校区id',
itcast_school_name             STRING COMMENT '校区name',
origin_channel                 STRING COMMENT '来源渠道',
tdepart_id                    INT COMMENT '咨询中心id',
department_name                     STRING COMMENT '咨询中心name',
dep_hour_customer_count                  BIGINT  COMMENT '咨询中心小时意向用户数',
dep_day_customer_count                   BIGINT  COMMENT '咨询中心天意向用户数',
dep_month_customer_count                 BIGINT  COMMENT '咨询中心月意向用户数',
dep_year_customer_count                  BIGINT  COMMENT '咨询中心年意向用户数',
channel_hour_customer_count              BIGINT  COMMENT '渠道小时意向用户数',
channel_day_customer_count               BIGINT  COMMENT '渠道天意向用户数',
channel_month_customer_count             BIGINT  COMMENT '渠道月意向用户数'  ,
channel_year_customer_count              BIGINT  COMMENT '渠道年意向用户数' ,
sch_hour_customer_count                   BIGINT     COMMENT '校区小时意向用户数' ,
sch_day_customer_count                    BIGINT     COMMENT '校区天意向用户数' ,
sch_month_customer_count                  BIGINT     COMMENT '校区月意向用户数' ,
sch_year_customer_count                   BIGINT     COMMENT '校区年意向用户数' ,
sub_hour_customer_count                   BIGINT    COMMENT '学科小时意向用户数'   ,
sub_day_customer_count                    BIGINT    COMMENT '学科天意向用户数'   ,
sub_month_customer_count                  BIGINT    COMMENT '学科月意向用户数'  ,
sub_year_customer_count                   BIGINT    COMMENT '学科年意向用户数' ,
area_hour_customer_count                  BIGINT     COMMENT '地区小时意向用户数'    ,
area_day_customer_count                   BIGINT     COMMENT '地区天意向用户数'    ,
area_month_customer_count                 BIGINT     COMMENT '地区月意向用户数'      ,
area_year_customer_count                  BIGINT     COMMENT '地区年意向用户数'     ,
 base_hour_customer_count        BIGINT COMMENT 'O2O+新老用户小时意向用户数'   ,
 base_day_customer_count         BIGINT COMMENT 'O2O+新老用户天意向用户数'   ,
 base_month_customer_count       BIGINT COMMENT 'O2O+新老用户月意向用户数'     ,
 base_year_customer_count        BIGINT COMMENT 'O2O+新老用户年意向用户数'    ,
o2o_hour_customer_count      BIGINT COMMENT 'O2O小时意向用户数',
o2o_day_customer_count       BIGINT COMMENT 'O2O天意向用户数' ,
o2o_month_customer_count      BIGINT COMMENT 'O2O月意向用户数' ,
o2o_year_customer_count      BIGINT COMMENT 'O2O年意向用户数' ,
 hour_customer_count             BIGINT COMMENT '总小时意向用户数'    ,
day_customer_count               BIGINT COMMENT '总天意向用户数'   ,
month_customer_count             BIGINT COMMENT '总月意向用户数'     ,
year_customer_count              BIGINT COMMENT '总年意向用户数'    ,
group_type                         STRING COMMENT '分组标记',
time_type                             STRING COMMENT '时间标记'
)comment '用户意向表'
row format delimited FIELDS TERMINATED BY '\t'
STORED AS orc TBLPROPERTIES('orc.compress'='SNAPPY');

drop table if EXISTS intention_dws.dws_intention_without_dedup;
create table dws_intention_without_dedup(
yearinfo        STRING comment '年',
monthinfo         STRING comment '月',
dayinfo             STRING comment '日' ,
hourinfo                STRING comment '小时',
O2O                  STRING COMMENT 'O2O' ,
customer_mark           STRING COMMENT '新老客户',
area                     STRING COMMENT '地区' ,
itcast_subject_id          INT COMMENT '学科id' ,
itcast_subject_name         STRING COMMENT '学科name' ,
itcast_school_id               INT COMMENT '校区id',
itcast_school_name             STRING COMMENT '校区name',
origin_channel                 STRING COMMENT '来源渠道',
tdepart_id                    INT COMMENT '咨询中心id',
department_name                     STRING COMMENT '咨询中心name',
dep_hour_customer_count                  BIGINT  COMMENT '咨询中心小时意向用户数',
dep_day_customer_count                   BIGINT  COMMENT '咨询中心天意向用户数',
dep_month_customer_count                 BIGINT  COMMENT '咨询中心月意向用户数',
dep_year_customer_count                  BIGINT  COMMENT '咨询中心年意向用户数',
channel_hour_customer_count              BIGINT  COMMENT '渠道小时意向用户数',
channel_day_customer_count               BIGINT  COMMENT '渠道天意向用户数',
channel_month_customer_count             BIGINT  COMMENT '渠道月意向用户数'  ,
channel_year_customer_count              BIGINT  COMMENT '渠道年意向用户数' ,
sch_hour_customer_count                   BIGINT     COMMENT '校区小时意向用户数' ,
sch_day_customer_count                    BIGINT     COMMENT '校区天意向用户数' ,
sch_month_customer_count                  BIGINT     COMMENT '校区月意向用户数' ,
sch_year_customer_count                   BIGINT     COMMENT '校区年意向用户数' ,
sub_hour_customer_count                   BIGINT    COMMENT '学科小时意向用户数'   ,
sub_day_customer_count                    BIGINT    COMMENT '学科天意向用户数'   ,
sub_month_customer_count                  BIGINT    COMMENT '学科月意向用户数'  ,
sub_year_customer_count                   BIGINT    COMMENT '学科年意向用户数' ,
area_hour_customer_count                  BIGINT     COMMENT '地区小时意向用户数'    ,
area_day_customer_count                   BIGINT     COMMENT '地区天意向用户数'    ,
area_month_customer_count                 BIGINT     COMMENT '地区月意向用户数'      ,
area_year_customer_count                  BIGINT     COMMENT '地区年意向用户数'     ,
 base_hour_customer_count        BIGINT COMMENT 'O2O+新老用户小时意向用户数'   ,
 base_day_customer_count         BIGINT COMMENT 'O2O+新老用户天意向用户数'   ,
 base_month_customer_count       BIGINT COMMENT 'O2O+新老用户月意向用户数'     ,
 base_year_customer_count        BIGINT COMMENT 'O2O+新老用户年意向用户数'    ,
 hour_customer_count             BIGINT COMMENT '总小时意向用户数'    ,
day_customer_count               BIGINT COMMENT '总天意向用户数'   ,
month_customer_count             BIGINT COMMENT '总月意向用户数'     ,
year_customer_count              BIGINT COMMENT '总年意向用户数'    ,
group_type                         STRING COMMENT '分组标记',
time_type                             STRING COMMENT '时间标记'
)comment '用户意向表'
row format delimited FIELDS TERMINATED BY '\t'
STORED AS orc TBLPROPERTIES('orc.compress'='SNAPPY');

drop table if EXISTS intention_dws.dws_clue;
create table intention_dws.dws_clue(
yearinfo        STRING comment '年',
monthinfo         STRING comment '月',
dayinfo             STRING comment '日' ,
hourinfo                STRING comment '小时',
O2O                  STRING COMMENT 'O2O' ,
customer_mark           STRING COMMENT '新老客户',
area                     STRING COMMENT '地区' ,
itcast_subject_id          INT COMMENT '学科id' ,
itcast_subject_name         STRING COMMENT '学科name' ,
itcast_school_id               INT COMMENT '校区id',
itcast_school_name             STRING COMMENT '校区name',
sch_hour_clue_count                  BIGINT     COMMENT '校区小时线索数' ,
sch_day_clue_count                   BIGINT     COMMENT '校区天线索数' ,
sch_month_clue_count                 BIGINT     COMMENT '校区月线索数' ,
sch_year_clue_count                  BIGINT     COMMENT '校区年线索数' ,
sub_hour_clue_count                  BIGINT    COMMENT '学科小时线索数'   ,
sub_day_clue_count                   BIGINT    COMMENT '学科天线索数'   ,
sub_month_clue_count                 BIGINT    COMMENT '学科月线索数'  ,
sub_year_clue_count                  BIGINT    COMMENT '学科年线索数' ,
area_hour_clue_count                 BIGINT     COMMENT '地区小时线索数'    ,
area_day_clue_count                  BIGINT     COMMENT '地区天线索数'    ,
area_month_clue_count                BIGINT     COMMENT '地区月线索数'      ,
area_year_clue_count                 BIGINT     COMMENT '地区年线索数'     ,
base_hour_clue_count       BIGINT COMMENT 'O2O+新老用户小时线索数'   ,
base_day_clue_count        BIGINT COMMENT 'O2O+新老用户天线索数'   ,
base_month_clue_count      BIGINT COMMENT 'O2O+新老用户月线索数'     ,
base_year_clue_count       BIGINT COMMENT 'O2O+新老用户年线索数'    ,
hour_clue_count            BIGINT COMMENT '总小时线索数'    ,
day_clue_count              BIGINT COMMENT '总天线索数'   ,
month_clue_count            BIGINT COMMENT '总月线索数'     ,
year_clue_count            BIGINT COMMENT '总年线索数'    ,
group_type                         STRING COMMENT '分组标记',
time_type                             STRING COMMENT '时间标记'





)comment '用户线索表'
row format delimited FIELDS TERMINATED BY '\t'
STORED AS orc TBLPROPERTIES('orc.compress'='SNAPPY');

drop table if EXISTS intention_dws.dws_clue_valid;
create table intention_dws.dws_clue_valid(
yearinfo        STRING comment '年',
monthinfo         STRING comment '月',
dayinfo             STRING comment '日' ,
hourinfo                STRING comment '小时',
O2O                  STRING COMMENT 'O2O' ,
customer_mark           STRING COMMENT '新老客户',
area                     STRING COMMENT '地区' ,
itcast_subject_id          INT COMMENT '学科id' ,
itcast_subject_name         STRING COMMENT '学科name' ,
itcast_school_id               INT COMMENT '校区id',
itcast_school_name             STRING COMMENT '校区name',
sch_hour_clue_count                  BIGINT     COMMENT '校区小时用户数' ,
sch_day_clue_count                   BIGINT     COMMENT '校区小时线索数' ,
sch_month_clue_count                 BIGINT     COMMENT '校区天线索数' ,
sch_year_clue_count                  BIGINT     COMMENT '校区月线索数' ,
sub_hour_clue_count                  BIGINT     COMMENT '校区年线索数' ,
sub_day_clue_count                   BIGINT    COMMENT '学科小时线索数'   ,
sub_month_clue_count                 BIGINT    COMMENT '学科天线索数'   ,
sub_year_clue_count                  BIGINT    COMMENT '学科月线索数'  ,
area_hour_clue_count                 BIGINT    COMMENT '学科年线索数' ,
area_day_clue_count                  BIGINT     COMMENT '地区小时线索数'    ,
area_month_clue_count                BIGINT     COMMENT '地区天线索数'    ,
area_year_clue_count                 BIGINT     COMMENT '地区月线索数'      ,
base_hour_clue_count                 BIGINT     COMMENT '地区年线索数'     ,
base_day_clue_count        BIGINT COMMENT 'O2O+新老用户小时线索数'   ,
base_month_clue_count      BIGINT COMMENT 'O2O+新老用户天线索数'   ,
base_year_clue_count       BIGINT COMMENT 'O2O+新老用户月线索数'     ,
hour_clue_count            BIGINT COMMENT 'O2O+新老用户年线索数'    ,
o2o_hour_customer_count      BIGINT COMMENT 'O2O小时线索数',
o2o_day_customer_count       BIGINT COMMENT 'O2O天线索数' ,
o2o_month_customer_count      BIGINT COMMENT 'O2O月线索数' ,
o2o_year_customer_count      BIGINT COMMENT 'O2O年线索数' ,
day_clue_count             BIGINT COMMENT '总小时线索数'    ,
month_clue_count            BIGINT COMMENT '总天线索数'   ,
year_clue_count             BIGINT COMMENT '总月线索数'     ,
group_type                         STRING COMMENT '分组标记',
time_type                             STRING COMMENT '时间标记'

)comment '有效用户线索表'
row format delimited FIELDS TERMINATED BY '\t'
STORED AS orc TBLPROPERTIES('orc.compress'='SNAPPY');

drop table if EXISTS intention_dws.dws_intention2;
create table intention_dws.dws_intention2(
yearinfo        STRING comment '年',
monthinfo         STRING comment '月',
dayinfo             STRING comment '日' ,
hourinfo                STRING comment '小时',
O2O                  STRING COMMENT 'O2O' ,
customer_mark           STRING COMMENT '新老客户',
area                     STRING COMMENT '地区' ,
itcast_subject_id          INT COMMENT '学科id' ,
itcast_subject_name         STRING COMMENT '学科name' ,
itcast_school_id               INT COMMENT '校区id',
itcast_school_name             STRING COMMENT '校区name',
origin_channel                 STRING COMMENT '来源渠道',
tdepart_id                    INT COMMENT '咨询中心id',
department_name                     STRING COMMENT '咨询中心name',

intention_customer_count              BIGINT COMMENT '意向用户数'    ,
group_type                         STRING COMMENT '分组标记',
time_type                             STRING COMMENT '时间标记'
)comment '用户意向表'
row format delimited FIELDS TERMINATED BY '\t'
STORED AS orc TBLPROPERTIES('orc.compress'='SNAPPY');

drop table if EXISTS intention_dws.dws_clue2;
create table intention_dws.dws_clue2(
yearinfo        STRING comment '年',
monthinfo         STRING comment '月',
dayinfo             STRING comment '日' ,
hourinfo                STRING comment '小时',
O2O                  STRING COMMENT 'O2O' ,
customer_mark           STRING COMMENT '新老客户',
area                     STRING COMMENT '地区' ,
itcast_subject_id          INT COMMENT '学科id' ,
itcast_subject_name         STRING COMMENT '学科name' ,
itcast_school_id               INT COMMENT '校区id',
itcast_school_name             STRING COMMENT '校区name',
clue_count                  BIGINT     COMMENT '线索数' ,
group_type                         STRING COMMENT '分组标记',
time_type                             STRING COMMENT '时间标记'



)comment '用户线索表'
row format delimited FIELDS TERMINATED BY '\t'
STORED AS orc TBLPROPERTIES('orc.compress'='SNAPPY');

drop table if EXISTS intention_dws.dws_clue_valid2;
create table intention_dws.dws_clue_valid2(
yearinfo        STRING comment '年',
monthinfo         STRING comment '月',
dayinfo             STRING comment '日' ,
hourinfo                STRING comment '小时',
O2O                  STRING COMMENT 'O2O' ,
customer_mark           STRING COMMENT '新老客户',
area                     STRING COMMENT '地区' ,
itcast_subject_id          INT COMMENT '学科id' ,
itcast_subject_name         STRING COMMENT '学科name' ,
itcast_school_id               INT COMMENT '校区id',
itcast_school_name             STRING COMMENT '校区name',
valid_clue_count                  BIGINT     COMMENT '有效线索数' ,
group_type                         STRING COMMENT '分组标记',
time_type                             STRING COMMENT '时间标记'

)comment '有效用户线索表'
row format delimited FIELDS TERMINATED BY '\t'
STORED AS orc TBLPROPERTIES('orc.compress'='SNAPPY');
"